const db = require('../config/db');

class GasStation {
  // Get all gas stations with pagination and filtering
  static async getAll({ page = 1, pageSize = 10, keyword = '', filter = 'all', sort = 'distance', asc = 0 }) {
    const offset = (page - 1) * pageSize;
    
    let query = `
      SELECT * FROM gas_stations 
      WHERE 1=1
    `;
    
    const params = [];
    
    // Apply keyword search if provided
    if (keyword) {
      query += ` AND (name LIKE ? OR address LIKE ?)`;
      params.push(`%${keyword}%`, `%${keyword}%`);
    }
    
    // Apply sorting
    if (sort === 'distance') {
      query += ` ORDER BY distance ${asc === 1 ? 'ASC' : 'DESC'}`;
    } else if (sort === 'price') {
      query += ` ORDER BY price ${asc === 1 ? 'ASC' : 'DESC'}`;
    } else {
      query += ` ORDER BY id DESC`;
    }
    
    // Apply pagination
    query += ` LIMIT ? OFFSET ?`;
    params.push(parseInt(pageSize), offset);
    
    const [rows] = await db.query(query, params);
    
    // Get total count for pagination
    const [countResult] = await db.query(
      `SELECT COUNT(*) as total FROM gas_stations WHERE 1=1 ${keyword ? ' AND (name LIKE ? OR address LIKE ?)' : ''}`,
      keyword ? [`%${keyword}%`, `%${keyword}%`] : []
    );
    
    return {
      list: rows,
      pagination: {
        total: countResult[0].total,
        page: parseInt(page),
        pageSize: parseInt(pageSize)
      }
    };
  }
  
  // Get gas station by ID
  static async getById(id) {
    const [rows] = await db.query('SELECT * FROM gas_stations WHERE id = ?', [id]);
    if (rows.length === 0) {
      return null;
    }
    
    // Get services for this gas station
    const [services] = await db.query(`
      SELECT * FROM gas_station_services 
      WHERE gas_station_id = ?
    `, [id]);
    
    // Get available coupons for this gas station
    const [coupons] = await db.query(`
      SELECT c.* FROM coupons c
      WHERE c.type = 'gas_station' 
      AND c.store_id = ?
      AND c.expire_date >= CURDATE()
    `, [id]);
    
    return {
      ...rows[0],
      services,
      coupons
    };
  }
  
  // Create new gas station (admin only)
  static async create(stationData) {
    const { name, address, price, latitude, longitude, rating = 0 } = stationData;
    
    const [result] = await db.query(
      `INSERT INTO gas_stations (name, address, price, latitude, longitude, rating)
       VALUES (?, ?, ?, ?, ?, ?)`,
      [name, address, price, latitude, longitude, rating]
    );
    
    return result.insertId;
  }
  
  // Update gas station (admin only)
  static async update(id, stationData) {
    const { name, address, price, latitude, longitude, rating } = stationData;
    
    const [result] = await db.query(
      `UPDATE gas_stations 
       SET name = ?, address = ?, price = ?, latitude = ?, longitude = ?, rating = ?, updated_at = NOW()
       WHERE id = ?`,
      [name, address, price, latitude, longitude, rating, id]
    );
    
    return result.affectedRows > 0;
  }
  
  // Delete gas station (admin only)
  static async delete(id) {
    const [result] = await db.query('DELETE FROM gas_stations WHERE id = ?', [id]);
    return result.affectedRows > 0;
  }
  
  // Get nearby gas stations based on user location
  static async getNearby(latitude, longitude, radius = 5000, limit = 10) {
    // Calculate distance using Haversine formula
    const [rows] = await db.query(`
      SELECT *, 
      (
        6371000 * acos(
          cos(radians(?)) * cos(radians(latitude)) * cos(radians(longitude) - radians(?)) + 
          sin(radians(?)) * sin(radians(latitude))
        )
      ) AS distance
      FROM gas_stations
      HAVING distance < ?
      ORDER BY distance
      LIMIT ?
    `, [latitude, longitude, latitude, radius, limit]);
    
    return rows;
  }
  
  // Record a gas transaction
  static async recordTransaction(userId, stationId, amount) {
    const [result] = await db.query(
      `INSERT INTO gas_transactions (user_id, gas_station_id, amount)
       VALUES (?, ?, ?)`,
      [userId, stationId, amount]
    );
    
    return result.insertId;
  }
  
  // Get user's gas card balance
  static async getUserGasCardBalance(userId) {
    const [rows] = await db.query(
      `SELECT gas_card_balance FROM users WHERE id = ?`,
      [userId]
    );
    
    return rows.length > 0 ? rows[0].gas_card_balance : 0;
  }
  
  // Update user's gas card balance
  static async updateUserGasCardBalance(userId, amount) {
    const [result] = await db.query(
      `UPDATE users SET gas_card_balance = gas_card_balance + ? WHERE id = ?`,
      [amount, userId]
    );
    
    return result.affectedRows > 0;
  }
}

module.exports = GasStation; 